﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using ZK.Qymd_TransManage.ServiceReferenceApi;

namespace ZK.Qymd_TransManage.Test
{
    public partial class 数据库查询 : Form
    {
        public 数据库查询()
        {
            InitializeComponent();
        }


        //SqlConnection oledb = new SqlConnection(@"Data Source=\\SD-201903202036;Initial Catalog=dbo;Integrated Security=True");
        SqlConnection oledb = new SqlConnection(@"  Data Source=localhost;Initial Catalog=QCH;User Id=sa;Password=4023581; ");


        public ServiceReferenceApi.ApiLoadingResponseDto GetTruckInfo(ServiceReferenceApi.TruckInfoDto truckInfo)
        {

            truckInfo.起始时间 = "2019-09-03 00:00:00";
            truckInfo.结束时间 = "2019-09-24 00:00:00";
            truckInfo.车牌号 = "";
            Console.WriteLine(oledb);
            //@"Data Source = LS--20180714CSF\SQL2018; Initial Catalog = db_19; Integrated Security = True";
            //server=ip,1433;database=db;uid=sa;pwd=123456;
            EventLog log = new EventLog();
            log.Source = "zkkjbd";
            ServiceReferenceApi.ApiLoadingResponseDto reDto = new ServiceReferenceApi.ApiLoadingResponseDto();
            //try
            //{
            Console.WriteLine("打开数据库");
            oledb.Open();

            Console.WriteLine("已经打开数据库");

            //string s1 = "SELECT [车号],[毛重],[皮重],[净重],[货物名称],[发货单位],[收货单位],[运输单位],[毛重时间],[皮重时间],[作废标志],[毛重计量员],[皮重计量员],[称重日期] FROM Detail ";
            string s1 = "select ch as [车号] ," +
                "mz as [毛重] ," +
                 "pz as [皮重] ," +
                  "jz as [净重] ," +
                   "wzmc as [货物名称] ," +
                   "gf as [发货单位] ," +
                   "sf as [收货单位] ," +
                   "wsdw as [运输单位] ," +
                   "mzsj as [毛重时间] ," +
                   "pzsj as [皮重时间] ," +
                   "scbz as [作废标志] ," +
                   "mzjly as [毛重计量员] ," +
                   "pzjly as [皮重计量员] ," +
                   "sj as [称重日期] " +
                " from czjlb a  LEFT OUTER JOIN dbo.yhb f ON a.mzjly = f.yhbm  LEFT OUTER JOIN dbo.yhb f1 ON a.pzjly = f1.yhbm ";

            string s2 = "";
            if (truckInfo.车牌号 != "")
            {
                s2 = "where a.ch = '" + truckInfo.车牌号 + "' and  sj >=convert(datetime,'" + truckInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckInfo.结束时间 + "',102) ";

                // "where a.ch = '" + truckInfo.车牌号 + "' and  sj >=convert(datetime,'truckInfo.起始时间',102) and sj <=convert(datetime,'truckInfo.结束时间',102) ";
            }
            //truckInfo.车牌号 == "" && truckInfo.起始时间 != null && truckInfo.结束时间 != null
            else if (truckInfo.车牌号 == "")
            {
                s2 = "WHERE  sj >=convert(datetime,'" + truckInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckInfo.结束时间 + "',102)";
            }

            string sql = s1 + s2 + " ORDER BY sj DESC";
            Console.WriteLine(sql);
            SqlDataAdapter dbAdapter = new SqlDataAdapter(sql, oledb);
            //LoadingRecord lr = new LoadingRecord();
            DataTable dt = new DataTable("dt1");
            //dt.TableName = "TaskList";
            dbAdapter.Fill(dt);
            if (dt != null)
            {
                reDto.Success = true;
                reDto.Msg = "查询磅单记录成功";
                reDto.dt = dt;
                Console.WriteLine("查询磅单记录成功");
            }
            else if (dt == null)
            {
                reDto.Success = true;
                reDto.Msg = "磅单记录为空";
                Console.WriteLine("磅单记录为空");
            }
            else
            {
                reDto.Success = false;
                reDto.Msg = "查询磅单记录失败";
                Console.WriteLine("查询磅单记录失败");
            }
            oledb.Close();
            //}
            //catch (Exception ex)
            //{
            // log.WriteEntry(ex.Message + "\n" + ex.StackTrace);
            // }
            Console.WriteLine("数据 " + Newtonsoft.Json.JsonConvert.SerializeObject(reDto.dt));
            return reDto;
        }


        public ApiLoadingResponseDto GetSummaryInfo(TruckSummaryInfoDto truckSummaryInfo)
        {
            EventLog log = new EventLog();
            log.Source = "zkkjsum";
            ApiLoadingResponseDto reDto = new ApiLoadingResponseDto();
            try
            {
                oledb.Open();
                string s1 = " ";
                //truckSummaryInfo.煤种 != null && truckSummaryInfo.收货单位 != null && truckSummaryInfo.起始时间 != null && truckSummaryInfo.结束时间 != null
                if (truckSummaryInfo.煤种 != "" && truckSummaryInfo.收货单位 != "")
                {
                    s1 = " WHERE a.wzmc = '" + truckSummaryInfo.煤种 + "'AND sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102) ";

                }
                else if (truckSummaryInfo.煤种 == "" && truckSummaryInfo.收货单位 != "")
                {
                    s1 = " WHERE a.sf = '" + truckSummaryInfo.收货单位 + "'AND sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102) ";
                }
                else if (truckSummaryInfo.煤种 != "" && truckSummaryInfo.收货单位 == "")
                {
                    s1 = " WHERE a.wzmc = '" + truckSummaryInfo.煤种 + "'AND sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102) ";
                }
                else if (truckSummaryInfo.煤种 == "" && truckSummaryInfo.收货单位 == "")
                {
                    s1 = " WHERE sj >=convert(datetime,'" + truckSummaryInfo.起始时间 + "',102) and sj <=convert(datetime,'" + truckSummaryInfo.结束时间 + "',102)  ";
                }
                string sql = "SELECT sum(jz) AS 煤总量,count(ch)AS 车数,sf, wzmc " +
                    "from czjlb a  LEFT OUTER JOIN dbo.yhb f ON a.mzjly = f.yhbm  LEFT OUTER JOIN dbo.yhb f1 ON a.pzjly = f1.yhbm" +
                    " "
                    + s1 + "GROUP BY sf, wzmc";
                Console.WriteLine(sql);
                SqlDataAdapter dbAdapter = new SqlDataAdapter(sql, oledb);
                DataTable dt = new DataTable("dt1");
                dbAdapter.Fill(dt);
                if (dt != null)
                {
                    reDto.Success = true;
                    reDto.Msg = "查询磅单汇总成功";
                    reDto.dt = dt;
                }
                else if (dt == null)
                {
                    reDto.Success = true;
                    reDto.Msg = "磅单汇总记录为空";
                }
                else
                {
                    reDto.Success = false;
                    reDto.Msg = "查询磅单汇总失败";
                }
                oledb.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                log.WriteEntry(ex.Message + "\n" + ex.StackTrace);
                oledb.Close();
            }
            Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(reDto.dt));
            return reDto;
        }

        private void button1_Click(object sender, EventArgs e)
        {

            var a = GetTruckInfo(new ServiceReferenceApi.TruckInfoDto());
        }

        private void button2_Click(object sender, EventArgs e)
        {
            var aa = GetSummaryInfo(new TruckSummaryInfoDto());
        }
    }
}
